﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace DeansWsccProject.App_Code
{
    public class functions
    {
        private static string getConnectionString()
        {
            return "DeansWsccProjectConnectionString";
        }

        // Generic function for populating a Repeater
        public static void fillRepeater(Repeater controlId, string sqlString, object addWithValue)
        {
            OleDbConnection oleDbConn = getDbConnection();
            oleDbConn.Open();
            OleDbCommand cmd = new OleDbCommand(sqlString, oleDbConn);
            cmd.CommandType = CommandType.Text;
            if (addWithValue != null) { cmd.Parameters.AddWithValue("@f1", addWithValue); }
            OleDbDataReader dataReader = cmd.ExecuteReader();
            controlId.DataSource = dataReader;
            controlId.DataBind();
            cmd.Dispose();
            dataReader.Close();
            dataReader.Dispose();
            oleDbConn.Close();
            oleDbConn.Dispose();
        }
        
        // Generic function for populating an Ajax Control Toolkit Accordion
        public static void fillAccordion(AjaxControlToolkit.Accordion controlId, string sqlString, object addWithValue)
        {
            OleDbConnection oleDbConn = getDbConnection();
            oleDbConn.Open();
            OleDbCommand cmd = new OleDbCommand(sqlString, oleDbConn);
            cmd.CommandType = CommandType.Text;
            if (addWithValue != null) { cmd.Parameters.AddWithValue("@f1", addWithValue); }
            OleDbDataReader dataReader = cmd.ExecuteReader();
            controlId.DataSource = dataReader;
            controlId.DataBind();
            cmd.Dispose();
            dataReader.Close();
            dataReader.Dispose();
            oleDbConn.Close();
            oleDbConn.Dispose();
        }
        
        // Generic function for populating a gridview, formview, listview or detailsview
        public static void fillDataView(DataBoundControl controlId, string sqlString, object addWithValue)
        {
            OleDbConnection oleDbConn = getDbConnection();
            oleDbConn.Open();
            OleDbCommand cmd = new OleDbCommand(sqlString, oleDbConn);
            cmd.CommandType = CommandType.Text;
            if (addWithValue != null) { cmd.Parameters.AddWithValue("@f1", addWithValue); }
            OleDbDataReader dataReader = cmd.ExecuteReader();
            controlId.DataSource = dataReader;
            controlId.DataBind();
            cmd.Dispose();
            dataReader.Close();
            dataReader.Dispose();
            oleDbConn.Close();
            oleDbConn.Dispose();
        }

        public static OleDbConnection getDbConnection()
        {
            string connectionStr = getConnectionString();
            OleDbConnection oleDbConn = new OleDbConnection(ConfigurationManager.ConnectionStrings[connectionStr].ConnectionString);
            return oleDbConn;
        }

        public static string getRating(string ratingStr)
        {
            if (ratingStr != "")
            {
                int ratingInt100 = Convert.ToInt32(ratingStr);
                int ratingRemainder = ratingInt100 % 10;
                if (ratingRemainder >= 5) { ratingInt100 += 5; }
                int ratingInt = ratingInt100 / 10;
                string ratingSeqStr = "";
                for (int i = ratingInt; i >= 1; i--)
                {
                    ratingSeqStr += "<img src='/Images/FilledStar.png' />";
                }
                for (int i = 10 - ratingInt; i >= 1; i--)
                {
                    ratingSeqStr += "<img src='/Images/EmptyStar.png' />";
                }
                return ratingSeqStr;
            }
            else
            {
                return "Not Yet Rated";
            }
        }

        public static string getNoReviews(string eBookId)
        {
            string noReviewsStr = getDataFromTable("SELECT noReviews FROM eBookAverageRatings WHERE eBook_fkid = @f1", eBookId)[0];
            return noReviewsStr;
        }

        // Generic function to return a unique filename with specified extension string
        public static string getUniqueFilename(string fileExtension)
        {
            System.Guid myGUID = new System.Guid();
            myGUID = Guid.NewGuid();
            return myGUID.ToString() + "." + fileExtension;
        }

        // Generic function to get all required rows of data from only one colum of a table, referencing only zero or one parameters in SQL query, returning said data in form of string array
        public static string[] getDataFromTable(string sqlString, object addWithValue)
        {
            OleDbConnection oleDbConn = getDbConnection();
            oleDbConn.Open();
            OleDbCommand cmd = new OleDbCommand(sqlString, oleDbConn);
            cmd.CommandType = CommandType.Text;
            if (addWithValue != null) // An null addWithValue object[] passed to function indicates no parameter referenced in sqlString
            {
                cmd.Parameters.AddWithValue("@f1", addWithValue);
            }
            OleDbDataReader dataReader = cmd.ExecuteReader();
            List<string> dataList = new List<string>();
            while (dataReader.Read())
            {
                string rowValue = Convert.ToString(dataReader.GetValue(0));
                dataList.Add(rowValue);
            }
            dataReader.Close();
            dataReader.Dispose();
            cmd.Dispose();
            oleDbConn.Close();
            oleDbConn.Dispose();
            if (dataList.Count() == 0) { dataList.Add(""); }
            return dataList.ToArray();
        }

        // Generic function to execute SQL non-query, referencing any number of parameters inside an object array if required
        public static void putDataInTable(string sqlString, object[] findWithValues)
        {
            OleDbConnection oleDbConn = getDbConnection();
            oleDbConn.Open();
            OleDbCommand cmd = new OleDbCommand(sqlString, oleDbConn);
            cmd.CommandType = CommandType.Text;
            if (findWithValues != null) // A null findWithValue object[] passed to function indicates no parameter referenced in sqlString
            {
                for (int i = 0; i < findWithValues.Length; i++)
                {
                    cmd.Parameters.AddWithValue("@f" + (i + 1), findWithValues[i]);
                }
            }
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            oleDbConn.Close();
            oleDbConn.Dispose();
        }

        // Generic function to get last ID inserted into the specified table
        public static string getLastIdInserted(string fromTable)
        {
            OleDbConnection oleDbConn = getDbConnection();
            oleDbConn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT TOP 1 ID FROM " + fromTable + " ORDER BY ID DESC", oleDbConn);
            OleDbDataReader dataReader = cmd.ExecuteReader();
            dataReader.Read();
            string lastId = Convert.ToString(dataReader.GetValue(0));
            dataReader.Close();
            dataReader.Dispose();
            cmd.Dispose();
            oleDbConn.Close();
            oleDbConn.Dispose();
            return lastId;
        }
    }
}